package excelFileReader;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.swing.JFrame;
import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import clients.RPCClient;
import entities.ProductionOrder;





/**
 * @author Idan
 *This class is used to perform ETL on orders from 2015 onwards
 */
public class ExcelReaderOrders2 
{
	
	//private static ArrayList<Object> rowArrayList = new ArrayList<Object>();
	//public static RPCClient ordersClient = null;
	private static Connection conn = null;
	
	public static void main(String[] args) 
	{
		
		JOptionPane.showMessageDialog(new JFrame(),"המערכת כעת תחל טעינת פק''עות לבסיס הנתונים, יש להמתין לאישור בסוף הפעולה.","טעינת פק''עות לח.נ.י.ת",JOptionPane.INFORMATION_MESSAGE);
		
		  try
		  {
			conn = DriverManager.getConnection("jdbc:mysql://localhost/hanitdb?" +
			          "user=root&password=han1than1t");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		  
		int counter = 0;
		/*try
		{
			ordersClient = new RPCClient();
			if  (ordersClient== null)
			{
        		JOptionPane.showMessageDialog(new JFrame(),"שגיאה בחיבור לשרת","שגיאה",JOptionPane.ERROR_MESSAGE);
				System.exit(0);
			}

		}
		catch  (Exception e) 
		{
			JOptionPane.showMessageDialog(new JFrame(),"שגיאה בקריאת קובץ ההגדרות.","שגיאה",JOptionPane.ERROR_MESSAGE);
			System.exit(0);
			//e.printStackTrace();
		}
		*/
		
		HSSFWorkbook   wb;
		try 
		{
			wb = new HSSFWorkbook  (new FileInputStream("neworders2015.xls"));
			
			HSSFSheet sheet = wb.getSheetAt(0);
			
			int rows = sheet.getPhysicalNumberOfRows();
			System.out.println("Sheet " + wb.getSheetName(0) + "\" has " + rows	+ " row(s).");
				for (int r = 0; r < rows; r++)
				{
					ProductionOrder order = new ProductionOrder();
					HSSFRow row = sheet.getRow(r);
					if (row == null)
					{
						continue;
					}
		
					int cells = row.getPhysicalNumberOfCells();
					System.out.println("\nROW " + row.getRowNum() + " has " + cells	+ " cell(s).");
					
					
					int c = 0;
					HSSFCell cell = row.getCell(c);
					
					double orderID = cell.getNumericCellValue();
					
					int intOrderID;
					if (orderID >= 2147483647)
					{
						orderID = orderID % 10000000;
						intOrderID = Double.valueOf(orderID).intValue();
						System.out.println("After conversion: " + intOrderID);
					}
					else
					{
						intOrderID = Double.valueOf(cell.getNumericCellValue()).intValue();
						System.out.println("Without conversion: " + intOrderID);
					}
				
					order.setOrderID(intOrderID);
					System.out.println(order.getOrderID());
					
					c++;
					cell = row.getCell(c);
					order.setType(cell.getStringCellValue());
					
					c++;
					cell = row.getCell(c);
					order.setDiopter(Float.valueOf(String.valueOf(cell.getNumericCellValue())));
					
					c++;
					cell = row.getCell(c);
					
					Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((cell.getNumericCellValue()));					
					Format formatter = new SimpleDateFormat("dd/MM/yyyy");
					String completeDate = formatter.format(date);
					order.setDateAsString(completeDate);
					
					c++;
					cell = row.getCell(c);
					order.setQuantity((Double.valueOf(cell.getNumericCellValue()).intValue()));
					
					ArrayList<Object> message = new ArrayList<Object>();
					
					
					message.add("createNewOrder");
					message.add(order);
					
					try
					{
						Statement stmt = conn.createStatement();
						System.out.println("INSERT IGNORE INTO orders(orderID,type,diopter,orderDate,quantity,statusID) VALUES('" + order.getOrderID() + "','" + order.getType() +"','" + order.getDiopter() + "','" + order.getOrderDate() + "','" + order.getQuantity() + "','1');");
						stmt.executeUpdate("INSERT IGNORE INTO orders(orderID,type,diopter,orderDate,quantity,statusID) VALUES('" + order.getOrderID() + "','" + order.getType() +"','" + order.getDiopter() + "','" + order.getOrderDate() + "','" + order.getQuantity() + "','1');");
					//	ordersClient.call(message);
						counter++;
	
					}
					catch (Exception e)
					{
						JOptionPane.showMessageDialog(new JFrame(),"שגיאה בתקשורת עם השרת.","שגיאה",JOptionPane.ERROR_MESSAGE);
						System.exit(0);
						//e.printStackTrace();
					}
						/*
						switch (cell.getCellType())
						{
		
							case HSSFCell.CELL_TYPE_NUMERIC:
							{
							
								//	System.out.println ("Row No.: " + row.getRowNum ()+ " " + row.getCell(0).getNumericCellValue());
									value = "NUMERIC value=" + cell.getNumericCellValue();	   
								break;
							}
								
		
							case HSSFCell.CELL_TYPE_STRING:
								value = "STRING value=" + cell.getStringCellValue();
								break;
								
							
							default:
						}
						System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE="	+ value);
						*/
				}
							
		}
			catch (FileNotFoundException e) 
			{
				JOptionPane.showMessageDialog(new JFrame(),"קובץ פק''עות לא נמצא.","שגיאה",JOptionPane.ERROR_MESSAGE);
				System.exit(0);
				//e.printStackTrace();
			} catch (IOException e) 
			{
				JOptionPane.showMessageDialog(new JFrame(),"ארעה שגיאה בקריאת הקובץ הפק''עות.","שגיאה",JOptionPane.ERROR_MESSAGE);
				System.exit(0);
				//e.printStackTrace();
			}
		
		
		JOptionPane.showMessageDialog(new JFrame(),"טעינת פקע''ות הסתיימה סה''כ מספר הפק''עות שנטענו למערכת הוא:" + "\n" + counter,"אישור",JOptionPane.INFORMATION_MESSAGE);
		System.exit(0);
		
		}
	
}
